package cn.jdy.plugins.service;

import cn.jdy.framework.db.hibernate.BaseDao;
import cn.jdy.framework.log.BaseLog;
import cn.jdy.framework.util.StringUtil;
import cn.jdy.platform.service.ConfService;
import cn.jdy.plugins.entity.Live;
import cn.jdy.plugins.entity.Search;

import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.MatchMode;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Restrictions;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Service;

import com.mysql.jdbc.jdbc2.optional.SuspendableXAConnection;

import java.math.BigInteger;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author lutan
 * @title : 直播操作实现
 */
@Service
public class LiveService extends BaseDao<Live> {

	@Autowired
	private ConfService confService;

	/**
	 * 按条件查询
	 * 
	 * @param Live
	 * @return
	 */
	@Cacheable(value = "cache1", key = "'help_id' + #Live.id")
	public List<Live> query(Live Live ,String... date) {
		if (Live == null)
			return super.findAll();
		List<Live> list = null;

		if (Live.getiDisplayLength() > 0) {
			list = super.find(createFilter(Live, date), Live.getiDisplayStart(),
					Live.getiDisplayLength());
		} else {
			list = super.find(createFilter(Live, date));
		}

		return list;
	}

	@Cacheable(value = "cache1", key = "'help_count' + #Live.id")
	public Long count(Live Live, String... date) {
		return super.count(createFilter(Live,date));
	}

	/**
	 * 拼装条件
	 * 
	 * @param Live
	 * @param page
	 * @return
	 */
	private DetachedCriteria createFilter(Live Live, String... date) {
		DetachedCriteria dc = DetachedCriteria.forClass(entityClass);
		if (!StringUtil.isNullOrEmpty(Live.getAppid())) {
			dc.add(Restrictions.eq("appid", Live.getAppid()));
		}
		if (!StringUtil.isNullOrEmpty(Live.getName())) {
			dc.add(Restrictions.like("name", Live.getName(),
					MatchMode.ANYWHERE));
		}
		if (!StringUtil.isNullOrEmpty(Live.getMainid())) {
			dc.add(Restrictions.like("mainid", Live.getMainid(),
					MatchMode.ANYWHERE));
		}
		if (!StringUtil.isNullOrEmpty(Live.getUserid())) {
			dc.add(Restrictions.eq("userid", Live.getUserid()));
		}
		if (!StringUtil.isNullOrEmpty(Live.getStatus())) {
			dc.add(Restrictions.eq("status", Live.getStatus()));
		}
		if (date.length > 0 && !StringUtil.isNullOrEmpty(date[0])
				&& !StringUtil.isNullOrEmpty(date[1])) {
			dc.add(Restrictions.between("createTime", date[0], date[1]));
		}
		dc.addOrder(Order.desc("createTime"));
		return dc;
	}
	
/**
 * 修改直播线路
 * @param id
 * @param url
 * @param name
 */
	public void updatemian(String id,String url,String name){
		try {
			String sql="UPDATE live SET mainurl=:mainurl,mainname=:mainname,liveurl=:mainurl WHERE mainid=:mainid";
			SQLQuery sqlQuery = em().createSQLQuery(sql);
			sqlQuery.setParameter("mainurl", url);
			sqlQuery.setParameter("mainname", name);
			sqlQuery.setParameter("mainid", id);
			sqlQuery.executeUpdate();
		} catch (Exception e) {
			BaseLog.e(this.getClass(), "更新异常", e);
		}
		
	} 
	
	/**
	 * 修改备用线路
	 * @param id
	 * @param url
	 * @param name
	 */
		public void updateback(String id,String url,String name){
			try {
				String sql="UPDATE live SET backurl=:backurl,backname=:backname WHERE backid=:backid";
				SQLQuery sqlQuery = em().createSQLQuery(sql);
				sqlQuery.setParameter("backurl", url);
				sqlQuery.setParameter("backname", name);
				sqlQuery.setParameter("backid", id);
				sqlQuery.executeUpdate();
			} catch (Exception e) {
				BaseLog.e(this.getClass(), "更新异常", e);
			}
			
		} 
		
		
		
		/**
		 * 获取相关视频
		 * @return
		 */
		@SuppressWarnings("unchecked")
		public List<Live> queryRelatedlive(String lineId,String liveName,String startTime,String endTime,int displayleng,int displaystart){
			List<Live> list = new ArrayList<Live>();
			String allSql="";
			String sqlStart="";
			String lineStr[] = lineId.split(",");
			if(startTime!=null && endTime!=null && startTime!="" && endTime!=""){
				sqlStart = "select * from live  where name like '%"+liveName+"%' and createTime BETWEEN '"+startTime+"'  AND '"+endTime+"' and mainid in(";
			}else{
				sqlStart = "select * from live  where name like '%"+liveName+"%' and mainid in(";
			}
			
			String sql = "";
			for(String id : lineStr){
				sql="'"+id+"'"+","+sql;
			}
			if(""!= sql){
				sql = sql.substring(0,sql.length()-1);
				allSql  = sqlStart+sql+")"+ "LIMIT"+" "+displaystart+","+displayleng;
				SQLQuery query = super.em().createSQLQuery(allSql.toString()).addEntity(entityClass);
				list = query.list();
			}
			return list;	
		}


		/**
		 * 按线路查询直播间
		 * @param sql
		 * @return
		 */
		@SuppressWarnings("unchecked")
		public List<Live> querySiteMap(String ssql,String name,String status,String createTime, int iDisplayStart, int iDisplayLength) {
			List<Live> list = new ArrayList<Live>();
			try {
				String sql="SELECT  *  FROM live WHERE mainid IN ("+ssql+") "+name+" "+status+" "+ createTime+"ORDER BY id DESC limit "+ iDisplayStart+","+iDisplayLength;
				Query query = super.em().createSQLQuery(sql.toString())
						.addEntity(entityClass);
				list = query.list();
			} catch (Exception e) {
				BaseLog.e(this.getClass(), "查询异常", e);
			}
			return list;
		}
		
		/**
		 * 按id查询直播间
		 * @param sql
		 * @return
		 */
		@SuppressWarnings("unchecked")
		public List<Live> querySiteMap(String liveids) {
			List<Live> list = new ArrayList<Live>();
			try {
				String sql="SELECT  * FROM live WHERE id IN ("+liveids+") ";
				Query query = super.em().createSQLQuery(sql)
						.addEntity(Live.class);
				list = query.list();
			} catch (Exception e) {
				BaseLog.e(this.getClass(), "查询异常", e);
			}
			return list;
		}
		
		/**
		 * 查询数量
		 * @param ssql
		 * @param name
		 * @param status
		 * @param createTime
		 * @return
		 */
		public int countquery(String ssql,String name,String status,String createTime){
			int count=0;
			try {
				String sql="SELECT count(*) FROM live WHERE mainid IN ("+ssql+") "+name+" "+status+" "+ createTime;
				Query query = super.em().createSQLQuery(sql);
				count = Integer.parseInt(String.valueOf(query.uniqueResult()));
			} catch (Exception e) {
				BaseLog.e(this.getClass(), "查询异常", e);
			}
				
			return count;
		}
		

		/**
		   * web police
		   * 查询综合统计
		   * @return
		   */
		  public Map<String,Object> liveCountliveQuery(String orId) {
			  try {
				  Map<String,Object> map=new  HashMap<String,Object>();
				   	//查询 本月观看
				  String sql = "select  '本月观看' as yt,count(g.operateDesc) as zs  from visitlog g,(select id "
						 +" from live  a ,(select l.id as mainid from line l, organ o where  o.id=l.organid  and o.id='"+orId+"' ) b where a.mainid=b.mainid ) b where g.liveId=b.id and g.operateDesc='观看直播' and DATE_FORMAT( g.createTime, '%Y%m' ) = DATE_FORMAT( CURDATE( ) , '%Y%m' ) " ;
		    	      SQLQuery sqlQuery = super.em().createSQLQuery(sql);
		    	 // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
			      sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			      map.put("bylist", sqlQuery.list());
			  
			      String sql4 = "select  '本日观看' as yt,count(g.operateDesc) as zs  from visitlog g,(select id "
			    		   +" from live  a ,(select l.id as mainid from line l, organ o where  o.id=l.organid  and o.id='"+orId+"' ) b where a.mainid=b.mainid ) b where g.liveId=b.id and g.operateDesc='观看直播' and to_days( g.createTime) = to_days(now());" ;
			       SQLQuery  sqlQuery3 = super.em().createSQLQuery(sql4);
			    	 // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
				      sqlQuery3.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				      map.put("brlist", sqlQuery3.list());
				   
				  
				      String str="";
				      SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
				       
				        List list=new ArrayList<>();
				        List zllist=new ArrayList<>();
				        for (int i = -7; i < 0; i++) {
				       // Calendar lastDate = Calendar.getInstance();
				       // lastDate.roll(Calendar.DATE, i);//日期回滚7天
				        //str=sdf.format(lastDate.getTime());
				        str=  getDate(i);
				        String sql2="select count(g.operateDesc) as zs  from visitlog g,(select id "
					      	+ " from live  a ,(select l.id as mainid from line l, organ o where  o.id=l.organid  and o.id='"+orId+"' ) b where a.mainid=b.mainid ) b where g.liveId=b.id and g.operateDesc='观看直播'"
					      + " and   DATE_FORMAT( g.createTime, '%Y-%m-%d' ) = '"+str+"' ";
				        SQLQuery sqlQuery1 = super.em().createSQLQuery(sql2);
				        sqlQuery1.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
					      List<Map> list1= sqlQuery1.list();
					      Map<String,Object> map2=new  HashMap<String,Object>();
					      Map<String,BigInteger> map1=  list1.get(0);
					   //   SimpleDateFormat sdf2=new SimpleDateFormat("MM-dd");
					     //   String str2=sdf2.format(str);//转换合格
					    //  map2.put(str2,map1.get("zs").intValue()+"");
					      list.add(map1.get("zs").intValue());
					      zllist.add(str.split("-")[1]+"-"+str.split("-")[2]);
				    	}
				        map.put("qtlist", list); 
				        map.put("qtnamelist", zllist); 
				        List list2=new ArrayList<>();
				        List list3=new ArrayList<>();
				        for (int i = -30; i < 0; i++) {
				       //  Calendar lastDate = Calendar.getInstance();
				       // lastDate.roll(Calendar.DATE, i);//日期回滚30天
				        //str=sdf.format(lastDate.getTime());
				        	  str=  getDate(i);
				      String  sql3="select count(g.operateDesc) as zs  from visitlog g,(select id "
					      	+ " from live  a ,(select l.id as mainid from line l, organ o where o.id=l.organid  and o.id='"+orId+"' ) b where a.mainid=b.mainid ) b where g.liveId=b.id and g.operateDesc='观看直播'"
					      + "  and   DATE_FORMAT( g.createTime, '%Y-%m-%d' ) = '"+str+"' ";
				        SQLQuery sqlQuery2 = super.em().createSQLQuery(sql3);
				        sqlQuery2.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
					      List<Map> list1= sqlQuery2.list();
					    //  Map<String,Object> map2=new  HashMap<String,Object>();
					      Map<String,BigInteger> map1=  list1.get(0);
					        SimpleDateFormat sdf2=new SimpleDateFormat("MM-dd");
					      //  String str2=sdf2.format(str);
					     // map2.put(str2,map1.get("zs").intValue()+"");
					     // list3.add(str2);
					      list3.add(str.split("-")[1]+"-"+str.split("-")[2]);

					      list2.add(map1.get("zs").intValue());
				    	}
				        map.put("ylist", list2); 
				        map.put("ynamelist", list3); 
				        return  map; 
				  
		  } catch (Exception e) {
		      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
		    }
			   return null;
		  }

		/**
		   * web police
		   * 查询综合统计
		   * @return
		   */
		  public List<Map<String,Object>> liveCountQuery(Live obj,String fromDate,String toDate) {
		    try {
		    	//查询 直播开始时间，直播结束时间，直播时长
		    	  String sql = "select  id,fromDate,toDate,(TIME_TO_SEC(timediff(str_to_date(toDate,'%Y-%m-%d %H:%i:%s'),str_to_date(fromDate,'%Y-%m-%d %H:%i:%s' ))))/60 as zdata "
		    	  		+ "from live where id=? ";
		    	      SQLQuery sqlQuery = em().createSQLQuery(sql);
		    	      sqlQuery.setParameter(0,obj.getId());
		    	 // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
			      sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			    List<Map<String,Object>> list= sqlQuery.list();
			    Map<String,Object> map=  list.get(0);
			    
			    
				//查询 直播页面访问次数
		    	  String sql1 = "select count(liveId) as fwcs from visitlog where   liveId=?  and status=1 and operateDesc='进入直播间'   ";
		    	  SQLQuery sqlQuery1 = em().createSQLQuery(sql1);
		    	  sqlQuery1.setParameter(0,obj.getId());
		    	 // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		    	  sqlQuery1.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			      List<Map> list1= sqlQuery1.list();
			      Map<String,BigInteger> map1=  list1.get(0);
			      map.put("fwcs",map1.get("fwcs").intValue()+"");
			      
			  	//查询 直播页面访问人数
		    	  String sql2 = "select count(*) as fwrs from (select userNickName,count(userOpenId) as fwcs from visitlog where liveId=?  and status=1 and operateDesc='进入直播间'group by  userOpenId) tab ";
		    	  SQLQuery sqlQuery2 = em().createSQLQuery(sql2);    
		    	  sqlQuery2.setParameter(0,obj.getId());
		    	 // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
		    	  sqlQuery2.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
			      List<Map> list2= sqlQuery2.list();
			      Map<String,BigInteger> map2=  list2.get(0);
			      map.put("fwrs", map2.get("fwrs").intValue()+"");
			      
			      
				  	//直播观看次数
			    	  String sql3 = "select  count(operateDesc) as gkcs  from visitlog where liveId=?   and operateDesc='观看直播'   ";
			    	  SQLQuery	  sqlQuery3 = em().createSQLQuery(sql3);        
			    	  sqlQuery3.setParameter(0,obj.getId());
			    	 // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
			    	  sqlQuery3.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				      List<Map> list3= sqlQuery3.list();
				      Map<String,BigInteger> map3=  list3.get(0);
				      map.put("gkcs", map3.get("gkcs").intValue()+"");
				      
					  	//直播观看人数
			    	  String sql4= "select count(*)   as gkrs from (select userOpenId,count(userOpenId) from visitlog where liveId=? and  operateDesc='观看直播'   group by  userOpenId) tab  ";
			    	  SQLQuery  sqlQuery4 = em().createSQLQuery(sql4);      
			    	  sqlQuery4.setParameter(0,obj.getId());
			    	 // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
			    	  sqlQuery4.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				      List<Map> list4= sqlQuery4.list();
				      Map<String,BigInteger> map4=  list4.get(0);
				      map.put("gkrs", map4.get("gkrs").intValue()+"");
				      
					  	//评论条数
			    	  String sql5= "select count(id) as plcs from chat where   liveId=? ";
			    	  SQLQuery  sqlQuery5  = em().createSQLQuery(sql5);       
			    	  sqlQuery5.setParameter(0,obj.getId());
			    	 // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
			    	  sqlQuery5.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				      List<Map> list5= sqlQuery5.list();
				      Map<String,BigInteger> map5=  list5.get(0);
				      map.put("plcs", map5.get("plcs").intValue()+"");
				      
				    	//评论人数
			    	  String sql6= "select count(id) as plrs  from (select  id from chat where   liveId=? group by userOpenId) tab ";
			    	  SQLQuery  sqlQuery6 = em().createSQLQuery(sql6);     
			    	  sqlQuery6.setParameter(0,obj.getId());

			    	 // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
			    	  sqlQuery6.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				      List<Map> list6= sqlQuery6.list();
				      Map<String,BigInteger> map6=  list6.get(0);
				      map.put("plrs", map6.get("plrs").intValue()+"");
				     
			    
			    
			      return  list;
			    } catch (Exception e) {
			      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
			    }
			    return null;
			  }

			/**
			   * web police
			   * 查询省份统计
			   * @return
			   */
			  public List<Map<String,Object>> liveSfCountQuery(Live obj,String fromDate,String toDate) {
			    try {
			    	//查询 直播开始时间，直播结束时间，直播时长
			    	String sql = "select userProvince,count(userProvince)as couup  from  (select  a.userProvince as userProvince , count( b.userOpenId) as couup "
			    			+ "  from watchUser a ,VisitLog b  where a.userOpenId=b.userOpenId  and b.liveId=?  group by b.userOpenId) tab"
			    			+ "  group by  userProvince";
			    	/*  String sql = "select  a.userProvince as userProvince , count(a.userProvince) as couup   from watchUser a ,VisitLog b"
			    	  		+ " where a.userOpenId=b.userOpenId  and b.liveId=?  group by a.userProvince ";*/
			    	      SQLQuery sqlQuery = em().createSQLQuery(sql);
			    	      sqlQuery.setParameter(0,obj.getId());
			    	 // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
				      sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				      List<Map<String,Object>> list= sqlQuery.list();
				      Map<String,Object> map=  list.get(0);
				    
				      return  list;
				    } catch (Exception e) {
				      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
				    }
				    return null;
				  }

			  /**
			   * web police
			   * 查询城市统计
			   * @return
			   */
			  public List<Map<String,Object>> liveCsCountQuery(Live obj,String fromDate,String toDate) {
			    try {
			    	//查询 直播开始时间，直播结束时间，直播时长
			    	  String sql = "Select   userCity,count(userCity) as couup from (select  a.userCity as userCity , count(b.userOpenId ) as couup  "
			    	  		+ " from watchUser a ,VisitLog b  where a.userOpenId=b.userOpenId  and b.liveId=? group by b.userOpenId ) tab group by userCity ";
			    	      SQLQuery sqlQuery = em().createSQLQuery(sql);
			    	      sqlQuery.setParameter(0,obj.getId());
			    	 // 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
				      sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
				      List<Map<String,Object>> list= sqlQuery.list();
				      Map<String,Object> map=  list.get(0);

				      return  list;
				    } catch (Exception e) {
				      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
				    }
				    return null;
				  }
			  /**
			   * 查询性别
			   * @param obj
			   * @return
			   */
			  public  List<Map<String,Object>> liveXbCountQuery(Live obj,String fromDate,String toDate) {
					try {
						String sql = "SELECT a.fromDate fromDate ,a.toDate toDate,b.bb bb,c.cc cc,d.dd dd FROM(SELECT fromDate,toDate FROM live WHERE id=?)a,"
								+ "(SELECT count(DISTINCT(userOpenId)) bb FROM visitlog WHERE liveId=? AND `status`=1 AND userSex=1) b,"
								+ "(SELECT count(DISTINCT(userOpenId)) cc FROM visitlog WHERE liveId=? AND `status`=1 AND userSex=2) c,"
								+ "(SELECT count(DISTINCT(userOpenId)) dd FROM visitlog WHERE liveId=? AND `status`=1 AND userSex!=1 AND userSex!=2) d";
						Query query = super.em().createSQLQuery(sql);
						query.setParameter(0,obj.getId());
						query.setParameter(1,obj.getId());
						query.setParameter(2,obj.getId());
						query.setParameter(3,obj.getId());
						// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
						query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
						List<Map<String,Object>> list= query.list();
						Map<String,Object> map=  list.get(0);
						return  list;
				    } catch (Exception e) {
				      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
				    }
				    return null;
				}
			 //select * from (select date_sub('2016-08-01',interval -3 day) aa) a,(select count(userNickName) as bb from visitlog where liveId=2888 and `status`=1)b,(select count(DISTINCT(userNickName)) as cc from visitlog where liveId=2888 and `status`=1)c
			  /**
			   * 查询时间
			   * @param obj
			   * @return
			   */
			  public  Map<String,Object> liveSjCountQuery(Live obj,String fromDate,String toDate,int x) {
					try {
						String sql = "select date_format(a.aa,'%Y-%c-%d')  aa,b.bb bb,c.cc cc  from (select date_sub(?,interval -? day) aa) a,(select count(userNickName) as bb from visitlog where liveId=? and `status`=1 and  operateDesc='进入直播间' AND date_format(createTime,'%Y-%m-%d')=(select date_sub(?,interval -? day)))b,(select count(DISTINCT(userNickName)) as cc from visitlog where liveId=? and `status`=1 and  operateDesc='进入直播间'   AND date_format(createTime,'%Y-%m-%d')=(select date_sub(?,interval -? day)))c";
						Query query = super.em().createSQLQuery(sql);
						query.setParameter(0,fromDate);
						query.setParameter(1,x);
						query.setParameter(2,obj.getId());
						query.setParameter(3,fromDate);
						query.setParameter(4,x);
						query.setParameter(5,obj.getId());
						query.setParameter(6,fromDate);
						query.setParameter(7,x);
						// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
						query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
						List<Map<String,Object>> list= query.list();
						Map<String,Object> map=  list.get(0);
						return  map;
				    } catch (Exception e) {
				      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
				    }
				    return null;
				}
			  
			  /**
			   * 查询时段
			   * @param obj
			   * @return
			   */
			  public  Map<String,Object> liveSdCountQuery(Live obj,String fromDate,String toDate,int x,int y) {
					try {
						String sql="select date_format(a.aa,'%Y-%c-%d %H:%i:%s') aa ,date_format(b.bb,'%Y-%c-%d %H:%i:%s') bb ,c.cc cc from "+
								"(select date_sub(?,interval -? HOUR) aa) a,(select date_sub(?,interval -? HOUR) bb) b,"+
								"(select count(*) cc from visitlog where createTime between (select date_sub(?,interval -? HOUR)) and (select date_sub(?,interval -? HOUR)) AND liveId=? AND `status`=1 and  operateDesc='进入直播间')c";
								
						Query query = super.em().createSQLQuery(sql);
						query.setParameter(0,fromDate);
						query.setParameter(1,y);
						query.setParameter(2,fromDate);
						query.setParameter(3,x);
						query.setParameter(4,fromDate);
						query.setParameter(5,y);
						query.setParameter(6,fromDate);
						query.setParameter(7,x);
						query.setParameter(8,obj.getId());

						// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
						query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
						List<Map<String,Object>> list= query.list();
						Map<String,Object> map=  list.get(0);
						return  map;
				    } catch (Exception e) {
				      BaseLog.e(this.getClass(), "搜索模糊查询异常！", e);
				    }
				    return null;
				}
			  
				/**
				 * 按公众号的appid查询
				 * @param sql
				 * @return
				 */
				@SuppressWarnings("unchecked")
				public List<Map<String,Object>> queryOrganMap(String appid, int iDisplayStart, int iDisplayLength,String liveLevel) {
					List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
					try {
						String sql="select id,name,mainurl,status,createTime,'http://www.bee-live.cn/w/live/index/' as zburl,liveLevel,fromDate,toDate,background  from live  a ,(select l.id as mainid from line l, organ o where  o.id=l.organid  and o.appId=? ) b where a.mainid=b.mainid ";
							 if(!StringUtil.isNullOrEmpty(liveLevel)){
								 if(liveLevel.equals("3")){
									 sql =sql+" and a.liveLevel<=3";
								 }else if(liveLevel.equals("2")){
									 sql =sql+" and a.liveLevel<=2";
								 }else if(liveLevel.equals("1")){
									 sql =sql+" and a.liveLevel<=1";
								 }
							 }
						 sql =sql+" ORDER BY fromDate desc limit ?,?";
						Query query = super.em().createSQLQuery(sql);
						query.setParameter(0,appid);
						query.setParameter(1,iDisplayStart);
						query.setParameter(2,iDisplayLength);
						// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
						query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
						 list= query.list();
					
					} catch (Exception e) {
						BaseLog.e(this.getClass(), "查询异常", e);
					}
					return list;
				}
				
				/**
				 * 按直播的id查询是否接入法律平台
				 * @param sql
				 * @return
				 */
				@SuppressWarnings("unchecked")
				public List<Map<String,Object>> isflopen(Long id) {
					List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
					try {
						String sql="select id from live  a ,(select l.id as mainid from line l, organ o where  o.isfl=0 and  o.id=l.organid   ) b where a.mainid=b.mainid and id=?";
						Query query = super.em().createSQLQuery(sql);
						query.setParameter(0,id);
				
						// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
						query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
						 list= query.list();
					
					} catch (Exception e) {
						BaseLog.e(this.getClass(), "查询异常", e);
					}
					return list;
				}
				/**
				 * 按公众号的appid查询
				 * @param sql
				 * @return
				 */
				public int countOrganMap(String appid, int iDisplayStart, int iDisplayLength,String liveLevel){
					int count=0;
					try {
						String sql="select count(*)  from live  a ,(select l.id as mainid from line l, organ o where o.id=l.organid  and  o.id=l.organid  and o.appId=?  ) b where a.mainid=b.mainid ";
						 if(!StringUtil.isNullOrEmpty(liveLevel)){
							 if(liveLevel.equals("3")){
								 sql =sql+" and a.liveLevel<=3";
							 }else if(liveLevel.equals("2")){
								 sql =sql+" and a.liveLevel<=2";
							 }else if(liveLevel.equals("1")){
								 sql =sql+" and a.liveLevel<=1";
							 }
						 }
						
						 sql =sql+" ORDER BY id ";
						Query query = super.em().createSQLQuery(sql);
						query.setParameter(0,appid);
						count = Integer.parseInt(String.valueOf(query.uniqueResult()));
					} catch (Exception e) {
						BaseLog.e(this.getClass(), "查询异常", e);
					}
						
					return count;
				}
				
				
				/**
				 * 按直播ID
				 * @param sql
				 * @return
				 */
				@SuppressWarnings("unchecked")
				public  Map<String,Object>  queryLiveMap(long li) {
					 Map<String,Object>  list = new  HashMap<String,Object> ();
					try {
					String sql="select l.id as liveid,l.name as name,status,l.createTime as createTime,appUrl,fromDate,toDate,e.name as linename  from live l,line e where l.mainid=e.id and l.id=?";
						Query query = super.em().createSQLQuery(sql);
						query.setParameter(0,li);
				
						// 将查询寻结果转换为Map格式，解决多表查询或零散字段查询 映射取值问题
						query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
						 list=  (Map<String, Object>) query.list().get(0);
					
					} catch (Exception e) {
						BaseLog.e(this.getClass(), "查询异常", e);
					}
					return list;
				}
				
				public static void main(String[] args) {
					Calendar cal = Calendar.getInstance();
					int year = cal.get(Calendar.YEAR);
					int month = cal.get(Calendar.MONTH )+1;
					SimpleDateFormat format = new SimpleDateFormat("dd"); 

					System.out.println(year + " 年 " + month + " 月");
			        Calendar ca = Calendar.getInstance();    
			        ca.set(Calendar.DAY_OF_MONTH, ca.getActualMaximum(Calendar.DAY_OF_MONTH));  
			        String last = format.format(ca.getTime());
			        System.out.println(last);
			        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			 
			      //  System.out.println(sdf.format(lastDate.getTime()));
			        
			        String str = "";
			        for (int i = -30; i < 0; i++) {
			            Calendar lastDate = Calendar.getInstance();
			            lastDate.setTime(new Date());
			        	   System.out.println(i);
			        lastDate.roll(lastDate.DATE, i);//日期回滚7天
			        str=sdf.format(lastDate.getTime());
			        System.out.println(str+"+++");
			        System.out.println(lastDate.getTime()+"+++");
			    	}
			        System.out.println(getDate(-30) );
				}
				
				public static String getDate(int num) {
				    long time = System.currentTimeMillis() + (1000L * 60 * 60 * 24 * num);
				    String pattern = "yyyy-MM-dd";
				    Date date = new Date();
				    if (time > 0) {
				        date.setTime(time);
				    }
				    SimpleDateFormat format = new SimpleDateFormat(pattern);
				    return format.format(date);
				}
}
